package org.nerve.bigFileIO.mysql;

import com.mysql.jdbc.Statement;

import java.io.*;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * org.nerve.bigFileIO
 * Created by zengxm on 2016/8/24.
 */
public class MysqlFileImporter extends MysqlConnector{

	protected String lineStartWord="INSERT INTO";       //一行开始标识符

	public String getLineStartWord() {
		return lineStartWord;
	}

	public void setLineStartWord(String lineStartWord) {
		this.lineStartWord = lineStartWord;
	}

	public MysqlFileImporter(String url, String user, String password){
		super(url,user,password);
	}

	public void importWidthFile(String filePath) throws SQLException, FileNotFoundException {
		File file = new File(filePath);
		if(!file.exists()){
			throw new FileNotFoundException(file.getAbsolutePath());
		}
		initConnection();



		try(
				FileInputStream fis=new FileInputStream(file);
				BufferedInputStream bis = new BufferedInputStream(fis)
		) {
			PreparedStatement statement = connection.prepareStatement("LOAD DATA LOCAL INFILE '"+filePath+"' IGNORE INTO TABLE sprider.big_data_info");
			if(statement.isWrapperFor(com.mysql.jdbc.PreparedStatement.class)){
				com.mysql.jdbc.PreparedStatement preparedStatement=statement.unwrap(com.mysql.jdbc.PreparedStatement.class);

				preparedStatement.setLocalInfileInputStream(bis);
				preparedStatement.execute();
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}



	public void importDo(String filepath, long maxLine) throws FileNotFoundException, SQLException {
		File file = new File(filepath);
		if(!file.exists()){
			throw new FileNotFoundException(file.getAbsolutePath());
		}

		initConnection();

		PreparedStatement ps=connection.prepareStatement("");
		ps.setFetchSize(Integer.MIN_VALUE);

		int batchSize=50;

		List<String> lines=new ArrayList<>();
		try(
				FileInputStream fis=new FileInputStream(file);
				BufferedInputStream bis = new BufferedInputStream(fis);
				BufferedReader reader = new BufferedReader(new InputStreamReader(bis, "ISO-8859-1"),5*1024*1024)// 用5M的缓冲读取文本文件
		){
			long lineIndex=0;
			long curBatch=0;
			String line;
			while((line = reader.readLine()) != null){
				if(line.startsWith(lineStartWord)){
				line = line.replace("|^\\xEF\\xBB\\xBF|","").replace("\r\n","\n").replace("\r","\n");
				if(line.indexOf("DELIMITER")==0)
					line = line.replace("DELIMITER","");

				if(line.length()==0)
					continue;
				//执行sql插入
				ps.addBatch(line);
				curBatch++;
				lineIndex++;
				System.out.println("batch add "+lineIndex+" line.size="+line.length());

				if(curBatch>=batchSize){
					int[] results=ps.executeBatch();
					System.out.println("executeBatch.......,result size="+results.length);
					ps.clearBatch();

					curBatch=0;
				}

				if(lineIndex>maxLine){
					System.out.println("> maxLine "+maxLine+" , exit!");
					break;
				}
//
//
//
}


			}

			int[] results=ps.executeBatch();
			System.out.println("executeBatch.......,result size="+results.length);
		}catch (IOException e){
			e.printStackTrace();
		}
	}

	public void importDo(String filepath) throws FileNotFoundException, SQLException {
		File file = new File(filepath);
		if(!file.exists()){
			throw new FileNotFoundException(file.getAbsolutePath());
		}

		initConnection();

		Statement ps= (Statement) connection.createStatement();
		ps.setFetchSize(Integer.MIN_VALUE);

		List<String> lines=new ArrayList<>();
		try(
				FileInputStream fis=new FileInputStream(file);
				BufferedInputStream bis = new BufferedInputStream(fis);
				BufferedReader reader = new BufferedReader(new InputStreamReader(bis,"utf-8"),5*1024*1024)// 用5M的缓冲读取文本文件
		){
			long lineIndex=0;
			String line;
			while((line = reader.readLine()) != null){
				if(line.startsWith(lineStartWord)){
					//执行sql插入
					ps.execute(line);
					lineIndex++;
					System.out.println("insert into "+lineIndex);
				}
			}

			int[] results=ps.executeBatch();
			System.out.println("executeBatch.......,result size="+results.length);
		}catch (IOException e){
			e.printStackTrace();
		}
	}
}
